Generated code - Getting started with filtering, SelfServicing

Preface

One of the most powerful aspects of the generated code and the framework it forms is the ability to formulate filters and sort-clauses directly in your code and let the code evaluate them at runtime. This means that once the framework has been generated, developers working on business logic code can formulate specific filters to request only that information necessary for the task they're currently working on, without the requirement of a given filter in a special stored procedure.

When filters and sort clauses are used to fetch data from the persistent storage (database), the filters and sort clauses are transformed to SQL and embedded into the actual SQL query by the used Dynamic Query Engine and filters are fully parameterized. This makes sure execution plans are preserved by the database server's optimizer and at the same time the filters are not constructed with values concatenated into the SQL query itself, so no risks for SQL injection attacks.

This section describes the low-level API on which the Linq to LLBLGen Pro provider and the QuerySpec system are build on. It's important to know the basics of predicate classes and how to construct them to fully utilize the power of the full LLBLGen Pro runtime framework, even if you're using Linq or QuerySpec in most of your queries; some functionality, like updates based on filters, use predicates directly and require basic knowledge of the predicate system. It's not essential to use Linq or QuerySpec to query data using the LLBLGen Pro runtime framework, you can mix any of the three systems at will: you can write one query in Linq, another in QuerySpec and another using the low-level API, all will be translated to SQL using the same pipeline: QuerySpec and Linq queries are translated to the building blocks of the low-level API: predicates, relations, predicate expressions and relation collections.

Predicates and Predicate expressions

A predicate is effectively a clause used in a WHERE statement which will result in True or False, 'WHERE' itself is not part of the predicate. Predicates can be grouped in a predicate expression. Predicate expressions can also be grouped inside other predicate expressions. Predicates are placed inside a predicate expression with the operators 'And' and 'Or'. Predicate expressions can also be placed inside another predicate expression with the operators 'And' and 'Or'.

Filtering is the same for entities, typed views and typed lists as well as dynamic lists and projections of database data. To construct a predicate expression, add Predicate and PredicateExpression objects to a PredicateExpression object and pass that to one of the methods to retrieve data, e.g. entitycollection.GetMulti().

Below is an example of a nested WHERE clause with some predicate expressions.

... Some Select statement
WHERE
	(	
		Table1.Foo = @param1
		AND
		Table1.Bar = @param2
	)
	OR
	Table2.Bar2 = @param3

The full predicate expression used in the WHERE clause is:
(Table1.Foo = @param1 AND Table1.Bar = @param2) OR Table2.Bar2 = @param3.

The following predicates are found in this filter:

• Table1.Foo = @param1
• Table1.Bar = @param2
• Table2.Bar2 = @param3

There are 2 predicate expressions found:

1. (Table1.Foo = @param1 AND Table1.Bar = @param2)
2. (Table1.Foo = @param1 AND Table1.Bar = @param2) OR Table2.Bar2 = @param3

To formulate the filter correctly, we start by constructing an empty PredicateExpression instance, B. Let's assume param1 has the value "One", param2 has the value "Two" and param3 has the value "Three".


The easiest way to proceed is then to construct predicate expression A:


A is now constructed and we can add this predicate expression as a single predicate to the predicate expression B:


There is one predicate left, OR Table2.Bar2 = @param3. Let's add that one with the Or operator directly to B:


B now has been filled with the complete filter. To sum it up, below are the complete sections of code to construct the complete predicate expression


There is no maximum set for the number of predicate objects you can add to a predicate expression, nor has a maximum been set for the number of predicate expressions you can nest into each other. As a rule of thumb, every set of predicates that should be grouped together as a single boolean expression should be placed in a separate PredicateExpression object: the complete contents of a PredicateExpression object will be placed inside a '()' pair to group the predicates physically in the SQL query.

Creating and working with field objects

The filtering system of LLBLGen Pro uses predicate classes, which use entity field objects (or typed view field objects) to work with. LLBLGen Pro offers a convenient way to produce entity field objects: entitynameFields.FieldName, and typedviewnameFields.FieldName. Example: 


To utilize this feature, please add the following code to your code file:

Setting aliases, expressions and aggregates on fields

To set an aggregate function, an expression (See Field expressions and aggregates) or an object alias, you can use command chaining by using special methods to set the appropriate property: using the EntityField methods SetAggregateFunction(), SetExpression() and SetObjectAlias() you can write all assignments in one statement. Below an example for a filter to use in a Having clause: